package com.sxfq.loansupermarket.backsystem.modular.report.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.AppointmentInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SevenReportInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.WholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.*;
import com.sxfq.loansupermarket.backsystem.modular.report.service.impl.YyfxInfo;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

/**
 * 预约客户信息
 *
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/wholeHospital")
public class WholeHospitalController extends BaseController {

    @Autowired
    IWholeHospitalService service;

    @Autowired
    ICustomerService customerService;

    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/report/wholeHospital/";

    /**
     * 跳转到预约客户管理页面
     */
    @RequestMapping("")
    public String customer(Model model) {
        return PREFIX + "/wholeHospital.html";
    }

    @RequestMapping(value = "/to_getDimension/{dse}")
    public String to_nature_courtyard_add(@PathVariable String dse, Model model) {
        String dimensionSelection = dse.split(",")[0];
        String value = dse.split(",")[1];
        String startTime = dse.split(",")[2];
        String endTime = dse.split(",")[3];
        String sql = "select * from dimension_info a where a.dimension_name!='" + dimensionSelection + "'";
        List<SevenReportInfo> sevenReportList = sqlMapper.selectList(sql, SevenReportInfo.class);
        model.addAttribute("dimensionSelection", dimensionSelection);
        model.addAttribute("sevenReportList", sevenReportList);
        model.addAttribute("value", value);
        model.addAttribute("startTime", startTime);
        model.addAttribute("endTime", endTime);
        return PREFIX + "dimensionSelection_two.html";
    }

    /**
     * 跳转到考试列表
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object list(SearchWholeHospitalInfo searchInfo, HttpServletRequest request) {
        Page<WholeHospitalInfo> page = new PageFactory<WholeHospitalInfo>().defaultPage();
        try {
            List<WholeHospitalInfo> list = new ArrayList<WholeHospitalInfo>();
            if ("customerAge".equals(searchInfo.getDimensionSelection())) {
                list = service.getCustomerAgeList(searchInfo);
            } else if ("tcName".equals(searchInfo.getDimensionSelection())) {
                list = service.getTcNameList(searchInfo);
            } else if ("medium".equals(searchInfo.getDimensionSelection())) {
                list = service.getOneMediumList(searchInfo);
            } else if ("doctor".equals(searchInfo.getDimensionSelection())) {
                list = service.getDoctorList(searchInfo);
            } else if ("payService".equals(searchInfo.getDimensionSelection())) {
                list = service.getPayServiceList(searchInfo);
            } else if ("cities".equals(searchInfo.getDimensionSelection())) {
                list = service.getCitiesList(searchInfo);
            } else if ("channel".equals(searchInfo.getDimensionSelection())) {
                list = service.getOneChannelList(searchInfo);
            } else if ("occupation".equals(searchInfo.getDimensionSelection())) {
                list = service.getOccupation(searchInfo);
            } else if ("department".equals(searchInfo.getDimensionSelection())) {
                list = service.getDepartment(searchInfo);
            }
            page.setRecords(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * excel表导入
     */
    @RequestMapping(value = "/cs_upload")
    public String importcsupload(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\cs_upload" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                String sql = "INSERT INTO `consumer_statistics_info` (`medical_record_number`, `customer_name`, `total_amount`, " +
                        "`customer_sex`, `customer_age`, `customer_phone`, `one_level_source`, `two_level_source`," +
                        " `thr_level_source`, `final_diagnosis_time`) VALUES";
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh0 = hssfRow.getCell(0);
                    Cell xh1 = hssfRow.getCell(1);
                    Cell xh2 = hssfRow.getCell(2);
                    Cell xh3 = hssfRow.getCell(3);
                    Cell xh4 = hssfRow.getCell(4);
                    Cell xh5 = hssfRow.getCell(5);
                    Cell xh6 = hssfRow.getCell(6);
                    Cell xh7 = hssfRow.getCell(7);
                    Cell xh8 = hssfRow.getCell(8);
                    Cell xh9 = hssfRow.getCell(9);

                    int xh3s = 0;
                    if ("男".equals(xh3)) {
                        xh3s = 1;
                    } else {
                        xh3s = 2;
                    }
                    String xh2s = "";
                    if (xh2.toString() == null || ("").equals(xh2.toString())) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    String xh9s = "";
                    if (xh9.toString() == null || ("").equals(xh9.toString())) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if (rowNum != hssfSheet.getLastRowNum()) {
                        sql += "('" + xh0 + "','" + xh1 + "', " + xh2s + " ," + xh3s + "" +
                                ",'" + xh4 + "','" + xh5 + "','" + xh6 + "'" +
                                ",'" + xh7 + "', '" + xh8 + "' ," + xh9s + "),";
                    } else {
                        sql += "('" + xh0 + "','" + xh1 + "', " + xh2s + " ," + xh3s + "" +
                                ",'" + xh4 + "','" + xh5 + "','" + xh6 + "'" +
                                ",'" + xh7 + "','" + xh8 + "'," + xh9s + ");";
                    }
                }
                sqlMapper.insert(sql);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "import_close.html";
        } else {
            return PREFIX + "import_close.html";
        }
    }


    @RequestMapping(value = "/to_getSevenReport/{ddse}")
    public String caseInfoPage(@PathVariable String ddse, Model model) throws Exception {
        String dimensionSelection = ddse.split(",")[0];
        String dimensionSelections = ddse.split(",")[1];
        String value = ddse.split(",")[2];
        String startTime = ddse.split(",")[3];
        String endTime = ddse.split(",")[4];
        model.addAttribute("dimensionSelection", dimensionSelection);
        model.addAttribute("dimensionSelections", dimensionSelections);
        model.addAttribute("value", value);
        model.addAttribute("startTime", startTime);
        model.addAttribute("endTime", endTime);
        return PREFIX + "getSevenReport.html";
    }

    /**
     * excel表导入
     */
    @RequestMapping(value = "/ta_upload")
    public String importtaupload(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\ta_upload" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                String sql = "INSERT INTO `treatment_analysis_info` (`customer_name`, `medical_record_number`, `one_level_source`," +
                        " `registration_type`, `consulting_project`, `tc_name`, `doctor`, `is_deal`, `savings_card_recharge`," +
                        " `business_income`, `vip_charge`) VALUES";
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh0 = hssfRow.getCell(0);
                    Cell xh1 = hssfRow.getCell(1);
                    Cell xh2 = hssfRow.getCell(2);
                    Cell xh3 = hssfRow.getCell(3);
                    Cell xh4 = hssfRow.getCell(4);
                    Cell xh5 = hssfRow.getCell(5);
                    Cell xh6 = hssfRow.getCell(6);
                    Cell xh7 = hssfRow.getCell(7);
                    Cell xh8 = hssfRow.getCell(8);
                    Cell xh9 = hssfRow.getCell(9);
                    Cell xh10 = hssfRow.getCell(10);

                    String xh4s = "";
                    if (xh4.toString() == null || ("").equals(xh4.toString())) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh2 + "'";
                    }
                    String xh5s = "";
                    if (xh5.toString() == null || ("").equals(xh5.toString())) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    String xh8s = "";
                    if (xh8.toString() == null || ("").equals(xh8.toString())) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    String xh9s = "";
                    if (xh9.toString() == null || ("").equals(xh9.toString())) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    String xh10s = "";
                    if (xh10.toString() == null || ("").equals(xh10.toString())) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if (rowNum != hssfSheet.getLastRowNum()) {
                        sql += "('" + xh0 + "','" + xh1 + "', '" + xh2 + "' ,'" + xh3 + "'" +
                                ", " + xh4s + " , " + xh5s + " ,'" + xh6 + "'" +
                                ",'" + xh7 + "', " + xh8s + "  , " + xh9s + " , " + xh10s + " ),";
                    } else {
                        sql += "('" + xh0 + "','" + xh1 + "', '" + xh2 + "' ,'" + xh3 + "'" +
                                ", " + xh4s + " , " + xh5s + " ,'" + xh6 + "'" +
                                ",'" + xh7 + "', " + xh8s + "  , " + xh9s + " , " + xh10s + " );";
                    }
                }
                sqlMapper.insert(sql);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "import_close.html";
        } else {
            return PREFIX + "import_close.html";
        }
    }

    /**
     * excel表导入
     */
    @RequestMapping(value = "/bill_upload")
    public String importbillupload(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                String sql = "INSERT INTO `bill_info` (`medical_record_number`, `bill_id`, `outpatient_name`," +
                        " `customer_name`, `customer_age`, `customer_sex`, `customer_phone`, " +
                        " `customer_type`, `create_time`, `create_people`, `first_visit_date`, " +
                        " `registration_time`, `one_level_source`, `two_level_source`, " +
                        " `thr_level_source`, `customer_label`, `initial_review`, `registration_type`, " +
                        " `charge_state`, `charge_time`, `doctor`, `payee`, `pay_service`, `original_price`, " +
                        " `single_discount`, `single_discounts`, `receivables`, `cash_receipts`, `rerformance_income`, " +
                        " `prepaid_principal`, `gift_deduction`, `tc_name`, `vip_charge`, `arrears`, `remarks`, `revenue`, " +
                        " `total_discount`) VALUES";
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh0 = hssfRow.getCell(0);
                    Cell xh1 = hssfRow.getCell(1);
                    Cell xh2 = hssfRow.getCell(2);
                    Cell xh3 = hssfRow.getCell(3);
                    Cell xh4 = hssfRow.getCell(4);
                    Cell xh5 = hssfRow.getCell(5);
                    Cell xh6 = hssfRow.getCell(6);
                    Cell xh7 = hssfRow.getCell(7);
                    Cell xh8 = hssfRow.getCell(8);
                    Cell xh9 = hssfRow.getCell(9);
                    Cell xh10 = hssfRow.getCell(10);
                    Cell xh11 = hssfRow.getCell(11);
                    Cell xh12 = hssfRow.getCell(12);
                    Cell xh13 = hssfRow.getCell(13);
                    Cell xh14 = hssfRow.getCell(14);
                    Cell xh15 = hssfRow.getCell(15);
                    Cell xh16 = hssfRow.getCell(16);
                    Cell xh17 = hssfRow.getCell(17);
                    Cell xh18 = hssfRow.getCell(18);
                    Cell xh19 = hssfRow.getCell(19);
                    Cell xh20 = hssfRow.getCell(20);
                    Cell xh21 = hssfRow.getCell(21);
                    Cell xh22 = hssfRow.getCell(22);
                    Cell xh23 = hssfRow.getCell(23);
                    Cell xh24 = hssfRow.getCell(24);
                    Cell xh25 = hssfRow.getCell(25);
                    Cell xh26 = hssfRow.getCell(26);
                    Cell xh27 = hssfRow.getCell(27);
                    Cell xh28 = hssfRow.getCell(28);
                    Cell xh29 = hssfRow.getCell(29);
                    Cell xh30 = hssfRow.getCell(30);
                    Cell xh31 = hssfRow.getCell(31);
                    Cell xh32 = hssfRow.getCell(32);
                    Cell xh33 = hssfRow.getCell(33);
                    Cell xh34 = hssfRow.getCell(34);
                    Cell xh35 = hssfRow.getCell(35);
                    Cell xh36 = hssfRow.getCell(36);
                    int xh5s = 0;
                    if ("男".equals(xh5)) {
                        xh5s = 1;
                    } else {
                        xh5s = 2;
                    }
                    String xh8s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh19s = "";
                    String xh32s = "";
                    String xh36s = "";
                    if (xh8.toString() == null || ("").equals(xh8.toString())) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if (xh10.toString() == null || ("").equals(xh10.toString())) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if (xh11.toString() == null || ("").equals(xh11.toString())) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if (xh19.toString() == null || ("").equals(xh19.toString())) {
                        xh19s = "null";
                    } else {
                        xh19s = "'" + xh19 + "'";
                    }
                    if (xh32.toString() == null || ("").equals(xh32.toString())) {
                        xh32s = "'" + 0.0 + "'";
                    } else {
                        xh32s = "'" + xh32 + "'";
                    }
                    if (xh36.toString() == null || ("").equals(xh36.toString())) {
                        xh36s = "'" + 0.0 + "'";
                    } else {
                        xh36s = "'" + xh36 + "'";
                    }
                    if (rowNum != hssfSheet.getLastRowNum()) {
                        sql += "('" + xh0 + "','" + xh1 + "','" + xh2 + "','" + xh3 + "'" +
                                ",'" + xh4 + "'," + xh5s + ",'" + xh6 + "'" +
                                ",'" + xh7 + "'," + xh8s + ",'" + xh9 + "'," + xh10s + "," + xh11s + ",'" + xh12 + "'" +
                                ",'" + xh13 + "','" + xh14 + "','" + xh15 + "','" + xh16 + "','" + xh17 + "','" + xh18 + "'" +
                                "," + xh19s + ",'" + xh20 + "','" + xh21 + "','" + xh22 + "','" + xh23 + "','" + xh24 + "'" +
                                ",'" + xh25 + "','" + xh26 + "','" + xh27 + "','" + xh28 + "','" + xh29 + "','" + xh30 + "'" +
                                ",'" + xh31 + "'," + xh32s + ",'" + xh33 + "','" + xh34 + "','" + xh35 + "'," + xh36s + "),";
                    } else {
                        sql += "('" + xh0 + "','" + xh1 + "','" + xh2 + "','" + xh3 + "'" +
                                ",'" + xh4 + "'," + xh5s + ",'" + xh6 + "'" +
                                ",'" + xh7 + "'," + xh8s + ",'" + xh9 + "'," + xh10s + "," + xh11s + ",'" + xh12 + "'" +
                                ",'" + xh13 + "','" + xh14 + "','" + xh15 + "','" + xh16 + "','" + xh17 + "','" + xh18 + "'" +
                                "," + xh19s + ",'" + xh20 + "','" + xh21 + "','" + xh22 + "','" + xh23 + "','" + xh24 + "'" +
                                ",'" + xh25 + "','" + xh26 + "','" + xh27 + "','" + xh28 + "','" + xh29 + "','" + xh30 + "'" +
                                ",'" + xh31 + "'," + xh32s + ",'" + xh33 + "','" + xh34 + "','" + xh35 + "'," + xh36s + ");";
                    }
                }
                sqlMapper.insert(sql);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "import_close.html";
        } else {
            return PREFIX + "import_close.html";
        }
    }


    /**
     * excel表导入
     */
    @RequestMapping(value = "/oa_upload")
    public String importoaupload(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\oa_upload" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                String sql = "INSERT INTO `operation_analysis_info` (`medical_record_number`, `customer_name`, `customer_sex`, " +
                        "`customer_age`, `customer_phone`, `create_time`, `one_level_source`, `two_level_source`, " +
                        "`thr_level_source`, `customer_label`, `first_visit_date`, `last_visit_time`, `reservations_project`," +
                        " `consulting_project`, `create_people`, `reservations_time`, `initial_review`, `registration_time`," +
                        " `registration_type`, `doctor`, `reservations_tc`, `receivables`, `cash_receipts`, " +
                        "  `is_loss_contract`, `is_consumption`, `medical_status`, `tc_name`," +
                        " `reservation_type`) VALUES";
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh0 = hssfRow.getCell(0);
                    Cell xh1 = hssfRow.getCell(1);
                    Cell xh2 = hssfRow.getCell(2);
                    Cell xh3 = hssfRow.getCell(3);
                    Cell xh4 = hssfRow.getCell(4);
                    Cell xh5 = hssfRow.getCell(5);
                    Cell xh6 = hssfRow.getCell(6);
                    Cell xh7 = hssfRow.getCell(7);
                    Cell xh8 = hssfRow.getCell(8);
                    Cell xh9 = hssfRow.getCell(9);
                    Cell xh10 = hssfRow.getCell(10);
                    Cell xh11 = hssfRow.getCell(11);
                    Cell xh12 = hssfRow.getCell(12);
                    Cell xh13 = hssfRow.getCell(13);
                    Cell xh14 = hssfRow.getCell(14);
                    Cell xh15 = hssfRow.getCell(15);
                    Cell xh16 = hssfRow.getCell(16);
                    Cell xh17 = hssfRow.getCell(17);
                    Cell xh18 = hssfRow.getCell(18);
                    Cell xh19 = hssfRow.getCell(19);
                    Cell xh20 = hssfRow.getCell(20);
                    Cell xh21 = hssfRow.getCell(21);
                    Cell xh22 = hssfRow.getCell(22);
                    Cell xh23 = hssfRow.getCell(23);
                    Cell xh24 = hssfRow.getCell(24);
                    Cell xh25 = hssfRow.getCell(25);
                    Cell xh26 = hssfRow.getCell(26);
                    Cell xh27 = hssfRow.getCell(27);
                    int xh2s = 0;
                    if ("男".equals(xh2)) {
                        xh2s = 1;
                    } else {
                        xh2s = 2;
                    }
                    String xh5s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh15s = "";
                    String xh17s = "";
                    String xh21s = "";
                    String xh22s = "";
                    String xh23s = "";
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11 + "") == null || ("").equals((xh11 + ""))) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh15 + "") == null || ("").equals((xh15 + ""))) {
                        xh15s = "null";
                    } else {
                        xh15s = "'" + xh15 + "'";
                    }
                    if ((xh17 + "") == null || ("").equals((xh17 + ""))) {
                        xh17s = "null";
                    } else {
                        xh17s = "'" + xh17 + "'";
                    }
                    if ((xh21 + "") == null || ("").equals((xh21 + ""))) {
                        xh21s = "null";
                    } else {
                        xh21s = "'" + xh21 + "'";
                    }
                    if ((xh22 + "") == null || ("").equals((xh22 + ""))) {
                        xh22s = "null";
                    } else {
                        xh22s = "'" + xh22 + "'";
                    }
                    if ((xh23 + "") == null || ("").equals((xh23 + ""))) {
                        xh23s = "null";
                    } else {
                        xh23s = "'" + xh23 + "'";
                    }

                    if (rowNum != hssfSheet.getLastRowNum()) {
                        sql += "('" + xh0 + "','" + xh1 + "', '" + xh2 + "' ,'" + xh3 + "'" +
                                ",'" + xh4 + "'," + xh5s + ",'" + xh6 + "'" +
                                ",'" + xh7 + "','" + xh8 + "','" + xh9 + "'," + xh10s + "," + xh11s + ",'" + xh12 + "'" +
                                ",'" + xh13 + "','" + xh14 + "', " + xh15s + " ,'" + xh16 + "', " + xh17s + " ,'" + xh18 + "'" +
                                ",'" + xh19 + "','" + xh20 + "', " + xh21s + " , " + xh22s + " , " + xh23s + " ,'" + xh24 + "'" +
                                ",'" + xh25 + "','" + xh26 + "','" + xh27 + "'  ),";
                    } else {
                        sql += "('" + xh0 + "','" + xh1 + "', '" + xh2 + "' ,'" + xh3 + "'" +
                                ",'" + xh4 + "'," + xh5s + ",'" + xh6 + "'" +
                                ",'" + xh7 + "','" + xh8 + "','" + xh9 + "'," + xh10s + "," + xh11s + ",'" + xh12 + "'" +
                                ",'" + xh13 + "','" + xh14 + "', " + xh15s + " ,'" + xh16 + "', " + xh17s + " ,'" + xh18 + "'" +
                                ",'" + xh19 + "','" + xh20 + "', " + xh21s + " , " + xh22s + " , " + xh23s + " ,'" + xh24 + "'" +
                                ",'" + xh25 + "','" + xh26 + "','" + xh27 + "'  );";
                    }
                }
                sqlMapper.insert(sql);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "import_close.html";
        } else {
            return PREFIX + "import_close.html";
        }
    }


    @RequestMapping(value = "/bill_import")
    public String to_bill_import(Model model) {
        return PREFIX + "bill_import.html";
    }

    @RequestMapping(value = "/consumer_statistics")
    public String to_consumer_statistics(Model model) {
        return PREFIX + "consumer_statistics.html";
    }

    @RequestMapping(value = "/treatment_analysis")
    public String to_treatment_analysis(Model model) {
        return PREFIX + "treatment_analysis.html";
    }

    @RequestMapping(value = "/operation_analysis")
    public String to_operation_analysis(Model model) {
        return PREFIX + "operation_analysis.html";
    }

    /**
     * 导出excel表
     *
     * @param response
     */
    @RequestMapping(value = "/downloadExcelyyfx")
    public void downloadChannelStatisticExcel(HttpServletResponse response) {

        try {
            List<YyfxInfo> list = service.getYyfxList();
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("运营分析");

            for (int i = 0; i < 9; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("reservations_id");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("medical_record_number");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("customer_name");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("customer_sex");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("customer_age");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("customer_phone");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("field_tc");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("first_visit_date");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("arrival_time");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("one_medium");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("two_medium");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("thr_medium");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("archiving");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("filing_time");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("customer_label");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("appointment_pay_service");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("deal_status");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("receivables");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("cash_receipts");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("doctor");
            cell.setCellStyle(style);

            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                YyfxInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                String medicalRecordNumber = map.getMedicalRecordNumber();
                String customerName = map.getCustomerName();
                Integer customerSex = map.getCustomerSex();
                String sex = map.getSex();
                Integer customerAge = map.getCustomerAge();
                String customerPhone = map.getCustomerPhone();
                String createTime = map.getCreateTime();
                Double receivables = map.getReceivables();
                Double cashReceipts = map.getCashReceipts();
                String oneLevelSource = map.getOneLevelSource();
                String twoLevelSource = map.getTwoLevelSource();
                String thrLevelSource = map.getThrLevelSource();
                String customerLabel = map.getCustomerLabel();
                String firstVisitDate = map.getFirstVisitDate();
                String lastVisitTime = map.getLastVisitTime();
                String reservationsProject = map.getReservationsProject();
                String consultingProject = map.getConsultingProject();
                String createPeople = map.getCreatePeople();
                String reservationsTime = map.getReservationsTime();
                String initialReview = map.getInitialReview();
                String registrationTime = map.getRegistrationTime();
                String arrivalTime = map.getArrivalTime();
                String registrationType = map.getRegistrationType();
                String doctor = map.getDoctor();

                String reservationsTc = map.getReservationsTc();
                String rerformanceIncome = map.getRerformanceIncome();
                String reservationSource = map.getReservationSource();
                String isLossContract = map.getIsLossContract();
                String isConsumption = map.getIsConsumption();
                String isDeal = map.getIsDeal();
                String medicalStatus = map.getMedicalStatus();
                String tcName = map.getTcName();
                String reservationYype = map.getReservationYype();
                String dealStatus = map.getDealStatus();
                String id = map.getId();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 19; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(id);
                row.getCell(1).setCellValue(medicalRecordNumber);
                row.getCell(2).setCellValue(customerName);
                row.getCell(3).setCellValue(sex);
                row.getCell(4).setCellValue(customerAge);
                row.getCell(5).setCellValue(customerPhone);
                row.getCell(6).setCellValue(tcName);
                row.getCell(7).setCellValue(firstVisitDate);
                row.getCell(8).setCellValue(arrivalTime);
                row.getCell(9).setCellValue(oneLevelSource);
                row.getCell(10).setCellValue(twoLevelSource);
                row.getCell(11).setCellValue(thrLevelSource);
                row.getCell(12).setCellValue(createPeople);
                row.getCell(13).setCellValue(arrivalTime);
                row.getCell(14).setCellValue(customerLabel);
                row.getCell(15).setCellValue(reservationsProject);
                row.getCell(16).setCellValue(dealStatus);
                row.getCell(17).setCellValue(receivables);
                row.getCell(18).setCellValue(cashReceipts);
                row.getCell(19).setCellValue(doctor);
            }

            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "/downloadExcelyyfxs")
    public void downloadChannelStatisticExcels(HttpServletResponse response) {
        try {
            List<YyfxInfo> list = service.getYyfxLists();
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("运营分析");

            for (int i = 0; i < 9; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("reservations_id");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("customer_name");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("customer_sex");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("customer_age");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("customer_phone");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("first_visit_date");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("one_medium");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("two_medium");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("thr_medium");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("archiving");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("filing_time");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("appointment_pay_service");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("deal_status");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("doctor");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("one_channel");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("two_channel");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("thr_channel");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("department");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                YyfxInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                String medicalRecordNumber = map.getMedicalRecordNumber();
                String reservationsId = map.getReservationsId();
                String customerName = map.getCustomerName();
                Integer customerSex = map.getCustomerSex();
                Integer customerAge = map.getCustomerAge();
                String customerPhone = map.getCustomerPhone();
                String createTime = map.getCreateTime();
                Double receivables = map.getReceivables();
                Double cashReceipts = map.getCashReceipts();
                String oneLevelSource = map.getOneLevelSource();
                String twoLevelSource = map.getTwoLevelSource();
                String thrLevelSource = map.getThrLevelSource();
                String filingTime = map.getFilingTime();
                String oneMedium = map.getOneMedium();
                String twoMedium = map.getTwoMedium();
                String thrMedium = map.getThrMedium();
                String oneChannel = map.getOneChannel();
                String twoChannel = map.getTwoChannel();
                String thrChannel = map.getThrChannel();
                String department = map.getDepartment();

                String customerLabel = map.getCustomerLabel();
                String firstVisitDate = map.getFirstVisitDate();
                String lastVisitTime = map.getLastVisitTime();
                String reservationsProject = map.getReservationsProject();
                String appointmentPayService = map.getAppointmentPayService();

                String consultingProject = map.getConsultingProject();
                String archiving = map.getArchiving();
                String reservationsTime = map.getReservationsTime();
                String initialReview = map.getInitialReview();
                String registrationTime = map.getRegistrationTime();
                String arrivalTime = map.getArrivalTime();
                String registrationType = map.getRegistrationType();
                String doctor = map.getDoctor();
                String reservationsTc = map.getReservationsTc();
                String rerformanceIncome = map.getRerformanceIncome();
                String reservationSource = map.getReservationSource();
                String isLossContract = map.getIsLossContract();
                String isConsumption = map.getIsConsumption();
                String isDeal = map.getIsDeal();
                String medicalStatus = map.getMedicalStatus();
                String tcName = map.getTcName();
                String reservationYype = map.getReservationYype();
                String dealStatus = map.getDealStatus();
                String id = map.getId();
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 17; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(reservationsId);
                row.getCell(1).setCellValue(customerName);
                row.getCell(2).setCellValue(customerSex);
                row.getCell(3).setCellValue(customerAge);
                row.getCell(4).setCellValue(customerPhone);
                row.getCell(5).setCellValue(firstVisitDate);
                row.getCell(6).setCellValue(oneMedium);
                row.getCell(7).setCellValue(twoMedium);
                row.getCell(8).setCellValue(thrMedium);
                row.getCell(9).setCellValue(archiving);
                row.getCell(10).setCellValue(filingTime);
                row.getCell(11).setCellValue(appointmentPayService);
                row.getCell(12).setCellValue(dealStatus);
                row.getCell(13).setCellValue(doctor);
                row.getCell(14).setCellValue(oneChannel);
                row.getCell(15).setCellValue(twoChannel);
                row.getCell(16).setCellValue(thrChannel);
                row.getCell(17).setCellValue(department);
            }

            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "/downLoad")
    public void downLoad(HttpServletResponse response) {
        try {
            List<AppointmentInfo> list = service.getYyList();
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("预约数据");

            for (int i = 0; i < 23; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("customer_name");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("customer_phone");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("customer_sex");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("customer_age");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("wechat_number");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("appointment_pay_service");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("content");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("media_from");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("engineFrom");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("engine_key");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("remarks");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("archiving");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("appointment_time");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("filing_time");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("jiedai_content");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("money");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("department");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("huifang");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("reservations_num");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("expenditure_project");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("status");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("statuss");
            cell.setCellStyle(style);
            cell = row.createCell(22);
            cell.setCellValue("doctor");
            cell.setCellStyle(style);

            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                AppointmentInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                String name = map.getName();
                String tel = map.getTel();
                String sex = map.getSex();
                String weixin = map.getWeixin();
                String disease = map.getDisease();
                String content = map.getContent();
                String memo = map.getMemo();
                String mediaFrom = map.getMediaFrom();
                String engineFrom = map.getEngineFrom();
                String engineKey = map.getEngineKey();
                String author = map.getAuthor();
                String orderDate = map.getOrderDate();
                String xiaofeiContent = map.getXiaofeiContent();
                String jiedaiContent = map.getJiedaiContent();
                String huifang = map.getHuifang();
                String addTime = map.getAddtime();
                Double xiaofei = map.getXiaofei();
                String part = map.getPart();
                String zhuanjiaNum = map.getZhuanjiaNum();
                String names = map.getNames();
                String isLocal = map.getIsLocal();
                String status = map.getStatus();
                String statuss = map.getStatuss();
                String age=map.getAge();
                String doctor=map.getDoctor();
                for (int j = 0; j <= 23; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(name);
                row.getCell(1).setCellValue(tel);
                row.getCell(2).setCellValue(sex);
                row.getCell(3).setCellValue(age);
                row.getCell(4).setCellValue(weixin);
                row.getCell(5).setCellValue(disease);
                row.getCell(6).setCellValue(content);
                row.getCell(7).setCellValue(mediaFrom);
                row.getCell(8).setCellValue(engineFrom);
                row.getCell(9).setCellValue(engineKey);
                row.getCell(10).setCellValue(memo);
                row.getCell(11).setCellValue(author);
                row.getCell(12).setCellValue(orderDate);
                row.getCell(13).setCellValue(addTime);
                row.getCell(14).setCellValue(jiedaiContent);
                row.getCell(15).setCellValue(xiaofei);
                row.getCell(16).setCellValue(part);
                row.getCell(17).setCellValue(huifang);
                row.getCell(18).setCellValue(zhuanjiaNum);
                row.getCell(19).setCellValue(xiaofeiContent);
                row.getCell(20).setCellValue(status);
                row.getCell(21).setCellValue(statuss);
                row.getCell(22).setCellValue(doctor);
//                row.getCell(3).setCellValue(memo);
//                row.getCell(4).setCellValue(xiaofeiContent);
//                row.getCell(5).setCellValue(jiedaiContent);
//                row.getCell(6).setCellValue(huifang);
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
